Missouri’s childcare deserts in 2023, by Zip Code Tabulation Area
Methodology
In the scipt etl/2_get_scta-2017-2021-acs.R, I retrieved the 5-Year ACS population estimates by ZCTA, and summed the age groups under six to get total amount of children five and under by ZCTA
The number of children five or under by ZCTA is from here on “demand” and I use it to create a ratio with the “supply,” which is licensed capacity of facilities by zip code, provided by ChildCareAware
In etl/3_zip-zcta-crosswalk-ChildCareAware.R, I crosswalked the zip code level data from ChildCareAware to ZCTA level data so that it can be properly compared to ACS population estimates by ZCTA
Any ZCTA where the demand is three times more than the supply is determined a “desert,” or in other words, any ZCTA where the children-to-slot ratio is equal to or more than 3:1
Any ZCTA where the ratio is equal to or more than 10:1 is “extreme” and marked in darker red for now on the map
Total number of children five or under in childcare deserts across Missouri in 2023
202,318 children of five or under across Missouri live in zip codes areas that are childcare deserts. This is almost half of children five or under.
Population in childcare deserts
Total population
Methodology
The number of children in deserts is the total amount of children in zctas with ratios greater than or equal to 3:1 OR any zip code with more than 50 children where there are no providers
How did childcare deserts change during the pandemic?
From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 80,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years.
The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic.
Number of children in childcare deserts by month and year
Number of childcare programs by month and year
Age breakouts of programs that recieved money for startup, expansion, or enhancement
Of programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children.
Potential difference between capacity and real number of slots
Of programs that received CRRSA funding for startup, expansion, or enhancement, several programs marked their anticipated capacity at 10 or more children less than the number of slots they added.
Did pandemic relief funding go to childcare desert zips we’ve identified?
Of the total of 229,363,334 dollars in the data we received, 55,014,524, or about fourth of it went to childcare deserts.
Methodology
In the script 4_clean-relief-data.R, I cleaned and parsed addresses for each application to connect it to a valid Missouri zip code. In a several instances, zip codes were typed in correctly, and I had to manually look up the address and correct the zip code. These instances are marked accordingly in the script.
I then concatenated all the relief data we received together, by program, amount and zip
Finally, I crosswalked the zip codes to ZCTAs so that I could then compare how much money went to ZCTAs labeled as a desert versus ones labeled as not a desert
Source Code
---title: "Findings: Disappearing Daycare"execute: output: false echo: falseformat: html: code-tools: true theme: cosmo toc: true---```{r}library(tidyverse)library(janitor)library(readxl)library(tidycensus)library(here)library(sf)library(lubridate)library(tmap)library(DT)# Cache the zip code tabulation data we want from the Census options(tigris_use_cache =TRUE)# Sets tmap to interactive modetmap_mode("view")```# Missouri's childcare deserts in 2023, by Zip Code Tabulation Area```{r}# LOAD DATA# Load clean and crosswalked data for all zctas from ChildCareAware surveychildcare_aware <-read_csv(here("data", "processed", "ChildCareAware_cleaned_and_crosswalked.csv")) %>%mutate(zcta =as.character(zcta)) # Load clean and crosswalked data for all zctas from ChildCareAware survey, with all other zctas and populations baked in childcare_supply_and_demand_2019_2023 <-read_csv(here("data", "processed", "childcare_supply_and_demand_2019_2023.csv")) %>%mutate(zcta =as.character(zcta))# Load all zctas not included in ChildCareAware survey # There are 1023 unique zctas in MO according to these crosswalkmo_zctas <-read_excel(here("data", "raw", "ZIPCodetoZCTACrosswalk2021UDS.xlsx"), sheet =1) %>%clean_names() %>%filter(state =="MO")# 5 year ACS estimates pf population five and under for all zctas in the countryacs <-read_csv(here("data", "processed", "pop_zcta_5acs.csv"))crrsa_startup_expansion <-read_excel(here("data","raw", "CRRSA Startup and Expansion with Paid.xlsx")) %>%clean_names()# Load all relief data with cleaned zips and years for when and where money was dispersed to relief_funding <-read_csv(here("data", "processed", "all_relief_cleaned.csv")) %>%mutate(zip =as.character(zip))# Join crosswalk with relief funding zips to that each zip matches a zcta to correspond to desert datarelief_zcta <- relief_funding %>%inner_join(mo_zctas, by =c("zip"="zip_code"))``````{r}supply_and_demand_2023 <- childcare_supply_and_demand_2019_2023 %>%filter(year =="2023") %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3& ratio <10~"desert", ratio >=10~"extreme", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert"))map_df <- supply_and_demand_2023 %>%mutate(status_num =case_when(status =="non-desert"~0, status =="desert"~1, status =="extreme"~3)) %>%select(zcta, status, status_num, ratio)#write.csv(map_df, "datawrapper_map.csv")``````{r}# JOIN CROSSWAKLED DATA WITH GEOM FOR MAPPING # Now we can use zctas to get geoms for mapping # Get the 2017-2021 5-year ACS ZTCAs for the whole US so that we can join these with zip codes and get geoms for mapping# B09001_005 is a dummy variable, we just need the geoms from the callzcta_geoms <-get_acs(geography ="zip code tabulation area", variables ="B09001_005", geometry =TRUE) %>%select(GEOID, geometry)# Join the survey and non-survey data with geoms from Census for mapping # This join to zcta geoms from ACS data drops 5 values, but anti join shows they are all non-deserts and "point" zctas for PO boxes map_df_sf <- map_df %>%inner_join(zcta_geoms, by =c("zcta"="GEOID")) %>%st_as_sf(sf_column_name ="geometry")``````{r}#| output: true# MAP DATA tm_shape(map_df_sf) +tm_polygons("status_num",palette ="Reds",legend.show =FALSE)```## Methodology - In the scipt `etl/2_get_scta-2017-2021-acs.R`, I retrieved the 5-Year ACS population estimates by ZCTA, and summed the age groups under six to get total amount of children five and under by ZCTA- The number of children five or under by ZCTA is from here on "demand" and I use it to create a ratio with the "supply," which is licensed capacity of facilities by zip code, provided by ChildCareAware- In `etl/3_zip-zcta-crosswalk-ChildCareAware.R`, I crosswalked the zip code level data from ChildCareAware to ZCTA level data so that it can be properly compared to ACS population estimates by ZCTA - Any ZCTA where the demand is three times more than the supply is determined a "desert," or in other words, any ZCTA where the children-to-slot ratio is equal to or more than 3:1- Any ZCTA where the ratio is equal to or more than 10:1 is "extreme" and marked in darker red for now on the map# Total number of children five or under in childcare deserts across Missouri in 2023202,318 children of five or under across Missouri live in zip codes areas that are childcare deserts. This is almost half of children five or under. ```{r}deserts_2023 <- supply_and_demand_2023 %>%filter(status %in%c("desert", "extreme")) %>%select(zcta, pop_5_and_under)# 202,318 in 227 zctas that are childcare desertssum(deserts_2023$pop_5_and_under, na.rm =TRUE)# Same five ZCTAs didn't join, but again, these are non-deserts PO boxes anywaymo_zctas_with_pop <- mo_zctas %>%left_join(acs, by =c("zcta"="GEOID")) %>%select(zcta, pop_5_and_under)# 442,254 children under 5 or under across the statesum(mo_zctas_with_pop$pop_5_and_under, na.rm=TRUE)percent_in_deserts <-202318/442254```### Population in childcare deserts ```{r}#| output: true#| column: pagedatatable(deserts_2023, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```### Total population```{r}#| output: true#| column: pagedatatable(mo_zctas_with_pop, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Methodology- The number of children in deserts is the total amount of children in zctas with ratios greater than or equal to 3:1 OR any zip code with more than 50 children where there are no providers # How did childcare deserts change during the pandemic?From 2019 to September 2020, the number of childcare programs decreased by 30%, leaving 80,000 more children in childcare deserts than the year before. During 2020, the number of children in childcare deserts was the highest it has reached in the last several years. The number of programs bounced back sharply by six months later though, and since 2021, the number of children in childcare deserts has steadily towards where it was pre-pandemic. ```{r}yearly_deserts <- childcare_supply_and_demand_2019_2023 %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3~"desert", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert")) %>%filter(status =="desert") %>%group_by(year, month) %>%summarize(children_in_deserts =sum(pop_5_and_under))num_programs_yearly <- childcare_aware %>%group_by(year, month) %>%summarize(num_program =sum(childcare_programs))pandemic_change_percent <- (192993-273570)/273570pandemic_change <-273570-192993```## Number of children in childcare deserts by month and year```{r}#| output: true#| column: pagedatatable(yearly_deserts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Number of childcare programs by month and year```{r}#| output: true#| column: pagedatatable(num_programs_yearly, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Age breakouts of programs that recieved money for startup, expansion, or enhancementOf programs that received CRRSA funding for startup, expansion, or enhancement, less than half as many slots were added for infant and toddlers as for preschool aged children. ```{r}age_breakouts <- crrsa_startup_expansion %>%select(number_of_infant_toddler_slots_adding, number_of_preschool_slots_adding, number_of_school_age_slots_adding) %>%mutate_all(~replace(., is.na(.), 0)) %>%pivot_longer(number_of_infant_toddler_slots_adding:number_of_school_age_slots_adding, names_to ="age_group", values_to ="number")%>%group_by(age_group) %>%summarize(number =sum(number))``````{r}#| output: true#| column: pagedatatable(age_breakouts, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Potential difference between capacity and real number of slotsOf programs that received CRRSA funding for startup, expansion, or enhancement, several programs marked their anticipated capacity at 10 or more children less than the number of slots they added. ```{r}real_capacity <- crrsa_startup_expansion %>%select(dvn, at_startup, number_of_infant_toddler_slots_adding, number_of_preschool_slots_adding, number_of_school_age_slots_adding, total_anticipated_capacity) %>%mutate(slots = number_of_infant_toddler_slots_adding + number_of_preschool_slots_adding + number_of_school_age_slots_adding) %>%filter(slots > total_anticipated_capacity) ``````{r}#| output: true#| column: pagedatatable(real_capacity, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```# Did pandemic relief funding go to childcare desert zips we've identified?Of the total of 229,363,334 dollars in the data we received, 55,014,524, or about fourth of it went to childcare deserts.```{r}# Filter the capacity deserts to data from 2021 and 2022 and remove non-desert zctassupply_and_demand_2021_2022 <- childcare_supply_and_demand_2019_2023 %>%filter(year %in%c("2021", "2022")) %>%mutate(ratio = pop_5_and_under / licensed_capacity) %>%mutate(status =case_when(ratio <3~"non-desert", ratio >=3~"desert", is.na(ratio) & pop_5_and_under >=50~"desert",is.na(ratio) & pop_5_and_under <50~"non-desert",TRUE~"non-desert")) # This join is for all funding together relief_to_desert_join <- relief_zcta %>%group_by(zcta, year) %>%summarize(total =sum(amount)) %>%left_join(supply_and_demand_2021_2022, by =c("zcta", "year")) ``````{r}# The big reveal total_desert_relief <- relief_to_desert_join %>%group_by(status) %>%summarize(total =sum(total))portion_to_deserts <-55014524/229363334``````{r}#| output: true#| column: pagedatatable(total_desert_relief, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))``````{r}# This join is for funding my source relief_to_desert_type_join <- relief_zcta %>%group_by(zcta, year, type) %>%summarize(total =sum(amount)) %>%left_join(supply_and_demand_2021_2022, by =c("zcta", "year")) %>%mutate(status =case_when(is.na(ratio) ~"non-desert", TRUE~"desert")) %>%group_by(type, status) %>%summarize(total =sum(total, na.rm =TRUE)) %>%pivot_wider(names_from = status, values_from = total)#write.csv(relief_to_desert_type_join, "datwrapper_stacked_bars.csv")``````{r}#| output: true#| column: pagedatatable(relief_to_desert_type_join, filter ='top', class ='cell-border stripe order-column', extensions ='Buttons',options =list(paging =TRUE,searching =TRUE,fixedColumns =TRUE,autoWidth =TRUE,ordering =TRUE,dom ='tB',buttons =c('copy', 'csv', 'excel') ))```## Methodology- In the script `4_clean-relief-data.R`, I cleaned and parsed addresses for each application to connect it to a valid Missouri zip code. In a several instances, zip codes were typed in correctly, and I had to manually look up the address and correct the zip code. These instances are marked accordingly in the script. - I then concatenated all the relief data we received together, by program, amount and zip - Finally, I crosswalked the zip codes to ZCTAs so that I could then compare how much money went to ZCTAs labeled as a desert versus ones labeled as not a desert